/**
 * Captain
 * © 2018 Captain copyright，All rights reserved.
 * http://www.sccaptain.com.cn
 * 
 * JAVA : 8
 * 文  件  名: ProjZoneManageDao.java
 * 创  建  人: QinYoubin (qin.youbin@sccaptain.com.cn)
 * 创建时间: 2018年1月15日 下午3:36:02
 * 版         本: 1.0.0
 * 备         注:
 * 修订历史:
 */
package com.material.c2c.basedata.dao;

import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.material.c2c.basedata.entity.DLXX_XZDY;
import com.material.c2c.basedata.entity.DLXX_XZDY_EXP;
import com.material.c2c.basedata.model.ZoneManageDTO;
import com.material.c2c.core.dao.EntityDao;

/**
 * 
 * @since 1.0
 * @version 1.0
 * @author QinYoubin (qin.youbin@sccaptain.com.cn)
 */
public interface ProjZoneManageDao extends EntityDao<DLXX_XZDY, String>{
 
  @Query("select new com.material.c2c.basedata.model.ZoneManageDTO(dx.id,dx.mc,dx.jc,dx.bm,dx.jb,dx.kzmc,dx.kzbm) from DLXX_XZDY dx where dx.sjid = :parentId")
  List<ZoneManageDTO> findSubZoneListByParrentId(@Param("parentId") String parentId);

  
  @Query("select new com.material.c2c.basedata.model.ZoneManageDTO(dx.id,dx.mc,dx.jc,dx.bm,dx.jb,dx.kzmc,dx.kzbm) from DLXX_XZDY dx where dx.sjid is null")
  List<ZoneManageDTO> findAllRootZoneList();

  
  @Query(nativeQuery = true, value = "select id,jb,bm,sjid from DLXX_XZDY start with id=:id connect by id = prior sjid")
  List<Object[]> findAllParentZoneListById(@Param("id") String id);


  void addZoneExpInfo(DLXX_XZDY_EXP expEntity);


  List<DLXX_XZDY> findByJbAndBmAndIdNot(int jb, String bm, String id);

  
  @Modifying
  @Query("update DLXX_XZDY_EXP set sjbm = :bm where sjid = :id")
  void updateZoneExpDataSJBM(@Param("id") String id, @Param("bm") String bm);

  
  @Modifying
  @Query("update DLXX_XZDY_EXP set zjbm = :bm where zjid = :id")
  void updateZoneExpDataZJBM(@Param("id") String id, @Param("bm") String bm);

  
  @Query(nativeQuery = true, value = "select id from DLXX_XZDY start with id=:id connect by prior id = sjid")
  List<String> findZoneIdsByZoneIdAndChildrenZoneId(@Param("id") String id);

  
  @Query(nativeQuery = true, value = "select id from QSSJ_FBF where szdy in (:ids)")
  List<String> findContracteeIdsByZoneIdAndChildrenZoneId(@Param("ids") List<String> ids);

  
  @Modifying
  @Query(nativeQuery = true, value = "delete from DLXX_XZDY where id in (:ids)")
  void deleteZoneByZoneIdAndChildrenZoneId(@Param("ids") List<String> ids);

 
  @Modifying
  @Query(nativeQuery = true, value = "delete from DLXX_XZDY_EXP where zjid in (:ids)")
  void deleteExpZoneByZoneIdAndChildrenZoneId(@Param("ids") List<String> ids);

  /**
   * 根据行政地域编码查询地域详细信息
   * @param bm 地域编码
   * @return
   */
  DLXX_XZDY findByBm(String bm);

  /**
   * 根据行政地域名称尾部匹配
   * @param mc 行政地域名称
   * @return
   */
  @Query(nativeQuery = true, value = "select * from DLXX_XZDY where mc=:mc")
  public DLXX_XZDY findByMc(@Param("mc") String mc);

  /**
   * 根据行政地域名称尾部匹配
   * @param likemc 地域编码
   * @return
   */
  @Query(nativeQuery = true, value = "select TOP(1) * from DLXX_XZDY where kzmc like '%'+:mc")
  public DLXX_XZDY findByMcTailMatching(@Param("mc") String mc);
  /**
   * 根据行政地域名称匹配
   * @param likemc 地域编码
   * @return
   */
  @Query(nativeQuery = true, value = "select TOP(1) * from DLXX_XZDY where kzmc+mc like '%'+:mc")
  public DLXX_XZDY findByKzmcMc(@Param("mc") String mc);
}
